package com.teamtwoseven.todo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Storage {

	public static final String DB_NAME = "master_db";
	public static final int DB_VERSION = 1;
	
	public static final String USER_TABLE ="userTable";	
	public static final String U_KEY_ROWID = "_id";
	public static final String U_KEY_USERNAME = "username";
	public static final String U_KEY_PASSWORD = "password";
	public static final String U_KEY_FULLNAME = "fullName";
	public static final String U_KEY_EMAIL = "email";

	public static final String I_KEY_ROWID = "_id";
	public static final String I_KEY_NAME = "itemName";
	public static final String I_KEY_DESCRIPTION = "description";
	public static final String I_KEY_CATEGORY = "name";
	public static final String I_KEY_DEADLINE = "deadline";
	public static final String I_KEY_FREQUENCY = "frequency";
	public static final String I_KEY_LOCATION = "location";
	public static final String I_KEY_ISDONE = "completed";
	
	private DbHelper helper;
	private final Context context;
	private SQLiteDatabase db;
	
	private static class DbHelper extends SQLiteOpenHelper{
		
		public DbHelper(Context context){
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + USER_TABLE + " (" +
					U_KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					U_KEY_USERNAME + " TEXT NOT NULL, " +
					U_KEY_PASSWORD + " TEXT NOT NULL, " +
					U_KEY_FULLNAME + " TEXT NOT NULL, " +
					U_KEY_EMAIL + " TEXT NOT NULL);"
					);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			//db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE);
			db.execSQL("DROP TABLE IF EXISTS *");
			onCreate(db);
		}
	}
	
	public Storage(Context c){
		this.context = c;
	}

	private Storage open(){
		helper = new DbHelper(context);
		db = helper.getWritableDatabase();
		return this;
	}
	private void close(){
		helper.close();
	}
	
	
	
	//---------------------------------------------------------------
	// Methods that write to database
	//---------------------------------------------------------------
	public long createUser(String username, String password, String fullName, String email){
		ContentValues cv = new ContentValues();
		cv.put(U_KEY_USERNAME, username);
		cv.put(U_KEY_PASSWORD, password);
		cv.put(U_KEY_FULLNAME, fullName);
		cv.put(U_KEY_EMAIL, email);
		
		open();
		long rowID = db.insert(USER_TABLE, null, cv);
		if(rowID > -1){
			db.execSQL("CREATE TABLE " + "itemsForUser" + rowID + " (" +
					I_KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					I_KEY_NAME + " TEXT NOT NULL, " +
					I_KEY_DESCRIPTION + " TEXT NOT NULL, " +
					I_KEY_CATEGORY + " TEXT NOT NULL, " +
					I_KEY_DEADLINE + " INTEGER NOT NULL, " + 
					I_KEY_FREQUENCY + " TEXT NOT NULL, " +
					I_KEY_LOCATION + " TEXT NOT NULL, " +
					I_KEY_ISDONE + " TEXT NOT NULL);"
					);
		}
		close();
		return rowID;
	}
	
	public long createItem(long userRowID, String name, String description, String category, long deadline, String frequency, String location){
		long retVal;
		
		ContentValues cv = new ContentValues();
		cv.put(I_KEY_NAME, name);
		cv.put(I_KEY_DESCRIPTION, description);
		cv.put(I_KEY_CATEGORY, category);
		cv.put(I_KEY_DEADLINE, deadline);
		cv.put(I_KEY_FREQUENCY, frequency);
		cv.put(I_KEY_LOCATION, location);
		cv.put(I_KEY_ISDONE, "false");
		
		open();
		retVal = db.insert("itemsForUser" + userRowID, null, cv);
		close();
		return retVal;
	}
	
	// For jUnit testing
	public long[] addItems(long userRowID, Item[] items) {
		long[] retVals = new long[0];
		
		if (items != null) {
			Item it;
			retVals = new long[items.length];
			for (int i = 0; i < items.length; i++) {
				it = items[i];
				retVals[i] = createItem(userRowID, it.getName(),
					it.getDescription(), it.getCategory(), it.getDeadline(),
					it.getFrequency(), it.getLocation());
			}
		}
		return retVals;
	}
	
	// For jUnit testing
	public long addItem(long userRowID, Item it) {
		long retVal = -1;
		
		if (it != null) {
			retVal = createItem(userRowID, it.getName(),
				it.getDescription(), it.getCategory(), it.getDeadline(),
				it.getFrequency(), it.getLocation());
		}
		return retVal;
	}
	
	public long updateItem(long userRowID, long itemID, String name, String description, String category, long deadline, String frequency, String location, String done){
		long retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(I_KEY_NAME, name);
		newVals.put(I_KEY_DESCRIPTION, description);
		newVals.put(I_KEY_CATEGORY, category);
		newVals.put(I_KEY_DEADLINE, deadline);
		newVals.put(I_KEY_FREQUENCY, frequency);
		newVals.put(I_KEY_LOCATION, location);
		newVals.put(I_KEY_ISDONE, done);
		
		open();
		retVal = db.update("itemsForUser" + userRowID, newVals, I_KEY_ROWID + "=" + itemID, null);
		close();
		return retVal;
	}
	
	public int setItemDone(long userRowID, long itemID){
		int retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(I_KEY_ISDONE, "true");
		
		open();
		retVal = db.update("itemsForUser" + userRowID, newVals, I_KEY_ROWID + "=" + itemID, null);
		close();
		return retVal;
	}
	
	public int setItemUndone(long userRowID, long itemID){
		int retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(I_KEY_ISDONE, "false");

		open();
		retVal = db.update("itemsForUser" + userRowID, newVals, I_KEY_ROWID + "=" + itemID, null);
		close();
		return retVal;
	}
	
	public int changePassword(long rowID, String newPass){
		int retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(U_KEY_PASSWORD, newPass);

		open();
		retVal = db.update(USER_TABLE, newVals, U_KEY_ROWID + "=" + rowID, null);
		close();
		return retVal;
	}

	public int changeEmail(long rowID, String newEmail){
		int retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(U_KEY_EMAIL, newEmail);

		open();
		retVal = db.update(USER_TABLE, newVals, U_KEY_ROWID + "=" + rowID, null);
		close();
		return retVal;
	}

	public int changeFullName(long rowID, String newName){
		int retVal;
		
		ContentValues newVals = new ContentValues();
		newVals.put(U_KEY_FULLNAME, newName);

		open();
		retVal = db.update(USER_TABLE, newVals, U_KEY_ROWID + "=" + rowID, null);
		close();
		return retVal;
	}
	
	
	//--------------------------------------------------------------------------------
	// Methods that delete from database
	//--------------------------------------------------------------------------------
	
	public boolean deleteUser(long userRowID){
		boolean retVal;
		
		open();
		retVal = db.delete(USER_TABLE, U_KEY_ROWID + "=" + userRowID, null) > 0;
		close();
		return retVal;
	}
	
	public boolean deleteItem(long userRowID, long itemRowID){
		boolean retVal;
		
		open();
		retVal = db.delete("itemsForUser" + userRowID, I_KEY_ROWID + "=" + itemRowID, null) > 0;
		close();
		return retVal;
	}
	
	// For jUnit testing
	public boolean deleteAllItems(long userRowID){
		boolean success = true;
		
		Item[] items = getAllItems(userRowID);
		
		for (int i = 0; i < items.length; i++) {
			success = success && deleteItem(userRowID, items[i].getItemID());
		}
		return success;
	}
	
	//--------------------------------------------------------------------------------
	// Item table getter methods
	//--------------------------------------------------------------------------------
	
	public Item getItem(long userRowID, long itemRowID){
		String[] columns = {I_KEY_ROWID, I_KEY_NAME, I_KEY_DESCRIPTION, I_KEY_CATEGORY, I_KEY_DEADLINE, I_KEY_FREQUENCY, I_KEY_LOCATION, I_KEY_ISDONE};
		
		open();
		Cursor c = db.query("itemsForUser" + userRowID,columns, I_KEY_ROWID + "=" + itemRowID, null, null, null, null);
		
		Item returnVal = null;
		if(c != null && c.moveToFirst()){
			returnVal = new Item(c.getLong(0), c.getString(1), c.getString(2), c.getString(3), c.getLong(4), c.getString(5), c.getString(6),Boolean.parseBoolean(c.getString(7)));
		}
		c.close();
		close();
		return returnVal;
	}
	
	public Item[] getAllItems(long userRowID){
		String[] columns = {I_KEY_ROWID, I_KEY_NAME, I_KEY_DESCRIPTION, I_KEY_CATEGORY, I_KEY_DEADLINE, I_KEY_FREQUENCY, I_KEY_LOCATION, I_KEY_ISDONE};
		
		open();
		Cursor c = db.query("itemsForUser" + userRowID,columns, I_KEY_ROWID + ">0", null, null, null, null);
		
		Item[] returnVal;
		if(c != null && c.moveToFirst()){
			returnVal = new Item[c.getCount()];
			int num = 0;
			while(num<c.getCount()){
				returnVal[num] = new Item(c.getLong(0), c.getString(1), c.getString(2), c.getString(3), c.getLong(4), c.getString(5), c.getString(6),Boolean.parseBoolean(c.getString(7)));
				c.moveToNext();
				num++;
			}
		}
		else{
			returnVal = new Item[0];
		}
		c.close();
		close();
		return returnVal;
	}
	
	public Item[] getSpecificItems(long userRowID, String whereClause){
		String[] columns = {I_KEY_ROWID, I_KEY_NAME, I_KEY_DESCRIPTION, I_KEY_CATEGORY, I_KEY_DEADLINE, I_KEY_FREQUENCY, I_KEY_LOCATION, I_KEY_ISDONE};
		
		open();
		Cursor c = db.query("itemsForUser" + userRowID, columns, whereClause, null, null, null, null);

		Item[] returnVal;
		if(c != null && c.moveToFirst()){
			returnVal = new Item[c.getCount()];
			int num = 0;
			while(num<c.getCount()){
				returnVal[num] = new Item(c.getLong(0), c.getString(1), c.getString(2), c.getString(3), c.getLong(4), c.getString(5), c.getString(6),Boolean.parseBoolean(c.getString(7)));
				c.moveToNext();
				num++;
			}
		}
		else{
			returnVal = new Item[0];
		}
		c.close();
		close();
		return returnVal;
	}
	
	
	
	
	//--------------------------------------------------------------------------------
	// User table getter methods
	//--------------------------------------------------------------------------------
	public long getRowID(String user){
		String[] columns = {U_KEY_ROWID, U_KEY_USERNAME, U_KEY_PASSWORD, U_KEY_FULLNAME, U_KEY_EMAIL};
		
		open();
		Cursor c = db.query(USER_TABLE,columns, U_KEY_USERNAME + "='" + user + "'", null, null, null, null);
		long returnVal = -1;
		if(c != null && c.moveToFirst()){
			returnVal = Long.parseLong(c.getString(0));
		}
		c.close();
		close();
		return returnVal;
	}
	
	public String getUser(long l){
		String[] columns = {U_KEY_ROWID, U_KEY_USERNAME, U_KEY_PASSWORD, U_KEY_FULLNAME, U_KEY_EMAIL};
		
		open();
		Cursor c = db.query(USER_TABLE,columns, U_KEY_ROWID + "=" + l, null, null, null, null);
		String returnVal = null;
		if(c != null && c.moveToFirst()){
			returnVal = c.getString(1);
		}
		c.close();
		close();
		return returnVal;
	}
	
	public String getPassword(long l){
		String[] columns = {U_KEY_ROWID, U_KEY_USERNAME, U_KEY_PASSWORD, U_KEY_FULLNAME, U_KEY_EMAIL};
		
		open();
		Cursor c = db.query(USER_TABLE,columns, U_KEY_ROWID + "=" + l, null, null, null, null);
		String returnVal = null;
		if(c != null && c.moveToFirst()){
			returnVal = c.getString(2);
		}
		c.close();
		close();
		return returnVal;
	}
	
	public String getFullName(long l){
		String[] columns = {U_KEY_ROWID, U_KEY_USERNAME, U_KEY_PASSWORD, U_KEY_FULLNAME, U_KEY_EMAIL};
		
		open();
		Cursor c = db.query(USER_TABLE,columns, U_KEY_ROWID + "=" + l, null, null, null, null);
		String returnVal = null;
		if(c != null && c.moveToFirst()){
			returnVal = c.getString(3);
		}
		c.close();
		close();
		return returnVal;
	}
	
	public String getEmail(long l){
		String[] columns = {U_KEY_ROWID, U_KEY_USERNAME, U_KEY_PASSWORD, U_KEY_FULLNAME, U_KEY_EMAIL};
		
		open();
		Cursor c = db.query(USER_TABLE,columns, U_KEY_ROWID + "=" + l, null, null, null, null);
		String returnVal = null;
		if(c != null && c.moveToFirst()){
			returnVal = c.getString(4);
		}
		c.close();
		close();
		return returnVal;
	}
}